package com.huajx.tuopuda.client.udp;

import com.huajx.core.db.SqliteHelper;
import com.huajx.core.db.exception.SqliteException;
import com.huajx.tuopuda.exception.BusinessException;
import com.huajx.util.CharUtil;
import com.huajx.util.StrUtil;

import java.sql.SQLException;

/**
 * @program: di-parent
 * @description:
 * @author: lisa 叶新华
 * @create: 2019-10-03 15:42
 */
public class AttendanceDatabase {

    private static SqliteHelper dbHelper;

    private static AttendanceDatabase me;

    public static AttendanceDatabase me() {
        if (me == null) {
            me = new AttendanceDatabase();
        }

        return me;
    }

    public void initDb(String schoolId){
        if(StrUtil.isBlank(schoolId)) {
            throw new BusinessException("未设置学校ID");
        }
        createSystemInfo();
        createDriveInfo();
        createCardInfo();

        initDbDate(schoolId);
    }

    private void findSystemInfoBySchoolId(String schoolId) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(*) FROM SystemInfo").append(CharUtil.LF);
        sb.append("WHERE schoolId = ?").append(CharUtil.LF);

//        getDbHelper().executeQuery(sb.toString(), )
    }

    private void initDbDate(String schoolId) {

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO SystemInfo VALUES ('").append(CharUtil.LF);
        sb.append(schoolId).append(CharUtil.LF);
        sb.append("')").append(CharUtil.LF);
        try {
            getDbHelper().executeUpdate(sb.toString());
        } catch (SQLException | ClassNotFoundException e) {
            throw new SqliteException("插入数据出错", e);
        }
    }

    public void createSystemInfo(){
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS SystemInfo").append(CharUtil.LF);
        sb.append("(schoolId CHAR(32) PRIMARY KEY NOT NULL").append(CharUtil.LF);
        sb.append(")").append(CharUtil.LF);
        createTable(sb);
    }

    public void createDriveInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS DriveInfo").append(CharUtil.LF);
        sb.append("(sn INT PRIMARY KEY NOT NULL").append(CharUtil.LF);
        sb.append(",ip CHAR(15)").append(CharUtil.LF);
        sb.append(",mask CHAR(15)").append(CharUtil.LF);
        sb.append(",gateway CHAR(15)").append(CharUtil.LF);
        sb.append(",mac CHAR(20)").append(CharUtil.LF);
        sb.append(",schoolId CHAR(32)").append(CharUtil.LF);
        sb.append(")").append(CharUtil.LF);
        createTable(sb);
    }

    public void createCardInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS CardInfo").append(CharUtil.LF);
        sb.append("(cardId INT PRIMARY KEY NOT NULL").append(CharUtil.LF);
        sb.append(", startTime INT").append(CharUtil.LF);
        sb.append(", endTime INT").append(CharUtil.LF);
        sb.append(", status INT DEFAULT 0").append(CharUtil.LF);
        sb.append(")").append(CharUtil.LF);
        createTable(sb);
    }

    public static SqliteHelper getDbHelper() {
        if (dbHelper == null) {
            try {
                dbHelper = new SqliteHelper("tuopuda");
            } catch (ClassNotFoundException e) {
                throw new SqliteException("", e);
            } catch (SQLException e) {
                throw new SqliteException("获取SQLite出错", e);
            }
        }
        return dbHelper;
    }


    private void createTable(StringBuilder sb) {
        try {
            getDbHelper().executeUpdate(sb.toString());
        } catch (SQLException e) {
            throw new SqliteException("创建表出错: " + sb.toString(), e);
        } catch (ClassNotFoundException e) {
            throw new SqliteException("创建表出错: " + sb.toString(), e);
        }
    }

}
